﻿using MSharp.Data.DatabaseInfo;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Text.RegularExpressions;

namespace MSharp.Data.Database
{
    public   class OracleDB : DB
    {
        public OracleDB(DBType dbType, DbProviderFactory dbFactory, string ConnectionString) 
            : base(dbType, dbFactory, ConnectionString)
        {
            this.Info = new OracleDBInfo(this);
        }

        public override bool ValidateSql(string strSql, out Exception ex)
        {
            bool bResult = false;
            ex = null;
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                conn.Open();
                try
                {
                    cmd.CommandText = "explain plan for " + strSql;
                    cmd.ExecuteNonQuery();
                    bResult = true;
                }
                catch (Exception e)
                {
                    ex = e;
                    bResult = false;
                }
                finally
                {
                    cmd?.Dispose();
                }
            }
            return bResult;
        }

        internal override Ret InsertGet<DT, Ret>(DT data, string tableName, params string[] excludeColNames)
        {
            var kv = InsertScript(data, tableName, excludeColNames);
            using (var conn = CreateConn())
            {
                var cmd = conn.CreateCommand();

                try
                {
                    PrepareCommand(cmd, conn, null, kv.Key, kv.Value, 30);

                    var result1 = cmd.ExecuteNonQuery();

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, result1);
                    }

                    OracleDBInfo OraInfo = Info as OracleDBInfo;

                    cmd.CommandText = SqlScript.IdentitySql(DBType, tableName, OraInfo.IdentitySeqName(tableName));

                    PrepareCommand(cmd, conn, null, cmd.CommandText, (object)null, 30);

                    var result2 = cmd.ExecuteScalar().ChangeType<Ret>();

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, null, result2);
                    }

                    return result2;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, cmd.Parameters, ex);
                    throw ex;
                }
            }
        }

        public override int InsertGetInt<DT>(DT data, string tableName, params string[] excludeColNames)
        {
            return InsertGet<DT, int>(data, tableName, excludeColNames);
        }

        public override long InsertGetLong<DT>(DT data, string tableName, params string[] excludeColNames)
        {
            return InsertGet<DT, long>(data, tableName, excludeColNames);
        }
        
        public override DataTable GetDataTableByPager(int currentPage, int pageSize, string selColumns, string joinTableName, string whereStr, string orderbyStr, out long totalCount)
        {
            if (string.IsNullOrEmpty(selColumns))
            {
                selColumns = "*";
            }

            if (currentPage <= 0)
            {
                currentPage = 1;
            }

            if (pageSize <= 0)
            {
                pageSize = 50;
            }

            string cntSQL = string.Empty, strPageSQL = string.Empty;
            DataTable data = new DataTable();
            totalCount = 0;

            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                //if (whereStr.ToLower().IndexOf(" where ")>-1)
                //{
                whereStr = Regex.Replace(whereStr, @"(\s)*(where|and)?(\s)*(.+)", "and $3$4", RegexOptions.Compiled | RegexOptions.IgnoreCase);
                //}
                //else if (whereStr.ToLower().StartsWith("and ") || whereStr.ToLower().IndexOf(" and ") > -1)
                //{
                //    whereStr = Regex.Replace(whereStr, @"(\s)*(and)?(\s)*(.+)", "$3$4", RegexOptions.Compiled | RegexOptions.IgnoreCase);
                //}
            }

            if (!string.IsNullOrWhiteSpace(orderbyStr))
            {
                orderbyStr = Regex.Replace(orderbyStr, @"(\s)*(order)(\s)+(by)(.+)", "$5", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            }
            else
            {
                throw new ArgumentNullException("orderbyStr");
            }

            cntSQL = "select count(1) from {0} where 1=1 {1}";
            cntSQL = string.Format(cntSQL, joinTableName, whereStr);

            string strSQL = "select {0} from {1} where 1=1 {2} order by {3}";
            strSQL = string.Format(strSQL, selColumns, joinTableName, whereStr, orderbyStr);


            strPageSQL = string.Format(@"SELECT * FROM (SELECT A.*, ROWNUM RN FROM ({0}) A) WHERE RN BETWEEN {1} AND {2}",
                                       strSQL, (currentPage - 1) * pageSize + 1, (currentPage) * pageSize);

            DataSet ds = new DataSet("ds");
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    cmd.CommandText = strPageSQL;
                    PrepareCommand(cmd, conn, null, cntSQL, (object)null, 300);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);

                    if (ds.Tables.Count > 0)
                    {
                        data = ds.Tables[0];
                    }

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, data);
                    }

                    cmd.CommandText = cntSQL;
                    PrepareCommand(cmd, conn, null, cntSQL, (object)null, 300);
                    totalCount = cmd.ExecuteScalar().ChangeType<long>();

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, totalCount);
                    }
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, cmd.Parameters, ex);
                    throw ex;
                }
            }
            return data;
        }
    }
}
